package com.example.hetong_system.repository;

import com.example.hetong_system.model.Contract;
import com.example.hetong_system.model.Customer;
import com.example.hetong_system.model.Employee;
import com.example.hetong_system.model.dto.ContractWithMilestoneDTO;
import com.example.hetong_system.model.enums.ContractStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface ContractRepository extends JpaRepository<Contract, Long> {
    List<Contract> findByStatus(String status);
    List<Contract> findByProjectManagerId(Long projectManagerId);
    List<Contract> findByCustomerId(Long customerId);

    Optional<Contract> findById(Long id);

    Contract save(Contract contract);

    List<Contract> findAll();

//    // 按项目经理和状态分页查询合同
//    Page<Contract> findByProjectManagerAndStatus(
//            Employee projectManager,
//            String status,  // 修改：使用String代替ContractStatus
//            Pageable pageable
//    );

    // 按项目经理和状态统计合同数量
    @Query("SELECT COUNT(c) FROM Contract c WHERE c.projectManager = ?1 AND c.status = ?2")
    Long countByProjectManagerAndStatus(Employee projectManager, String statusCode);

    // 按项目经理和状态分页查询合同（字符串状态版本）
    @Query("SELECT c FROM Contract c WHERE c.projectManager = ?1 AND c.status = ?2")
    Page<Contract> findByProjectManagerAndStatus(Employee projectManager, String statusCode, Pageable pageable);

    // 根据客户分页查询合同
    Page<Contract> findByCustomer(Customer customer, Pageable pageable);

    // 根据项目经理ID和状态分页查询合同（修改：使用String代替ContractStatus）
    Page<Contract> findByProjectManagerIdAndStatus(
            Long projectManagerId,
            String status,  // 修改：使用String代替ContractStatus
            Pageable pageable
    );

    // 根据项目经理ID和状态统计合同数量（修改：使用String代替ContractStatus）
    @Query("SELECT COUNT(c) FROM Contract c " +
            "WHERE c.projectManager.id = ?1 AND c.status = ?2")
    Long countByProjectManagerIdAndStatus(Long projectManagerId, String status);

    /**
     * 根据客户ID和合同状态分页查询合同
     */
    Page<Contract> findByCustomerIdAndStatus(
            Long customerId,
            String status,
            Pageable pageable
    );






    /**
     * 根据项目经理ID、状态和分页参数查询合同，并获取关联的里程碑状态
     */
    @Query("SELECT NEW com.example.hetong_system.model.dto.ContractWithMilestoneDTO(" +
            "c.id, c.status, c.name, c.createTime, c.updateTime, " +
            "c.projectManager, c.customer, m.status) " +
            "FROM Contract c " +
            "LEFT JOIN MilestonePlan m ON m.contractId = c.id " +
            "WHERE c.projectManager.id = ?1 AND c.status = ?2")
    Page<ContractWithMilestoneDTO> findByProjectManagerIdAndStatusWithMilestone(
            Long projectManagerId, String status, Pageable pageable);

    /**
     * 根据项目经理ID和状态统计合同数量，并获取关联的里程碑状态
     */
    @Query("SELECT COUNT(c) FROM Contract c " +
            "LEFT JOIN MilestonePlan m ON m.contractId = c.id " +
            "WHERE c.projectManager.id = ?1 AND c.status = ?2")
    Long countByProjectManagerIdAndStatusWithMilestone(Long projectManagerId, String status);

    /**
     * 根据ID查询合同，并获取关联的里程碑状态
     */
    @Query("SELECT NEW com.example.hetong_system.model.dto.ContractWithMilestoneDTO(" +
            "c.id, c.status, c.name, c.createTime, c.updateTime, " +
            "c.projectManager, c.customer, m.status) " +
            "FROM Contract c " +
            "LEFT JOIN MilestonePlan m ON m.contractId = c.id " +
            "WHERE c.id = ?1")
    Optional<ContractWithMilestoneDTO> findByIdWithMilestone(Long id);


    /**
     * 根据客户和状态分页查询合同
     */
    Page<Contract> findByCustomerAndStatus(
            Customer customer,
            String status,
            Pageable pageable
    );

    // 按项目经理和多个状态分页查询合同
    Page<Contract> findByProjectManagerAndStatusIn(
            Employee projectManager,
            List<String> statusCodes,
            Pageable pageable
    );

    /**
     * 检查指定合同的所有条款的审核状态是否均为APPROVED
     */
    @Query("SELECT CASE WHEN COUNT(c) = 0 THEN true ELSE false END " +
            "FROM ContractClause c " +
            "WHERE c.id.contractId = :contractId " +
            "AND (c.legalAuditStatus != 'APPROVED' " +
            "     OR c.financeAuditStatus != 'APPROVED' " +
            "     OR c.customerAuditStatus != 'APPROVED')")
    boolean checkAllClausesApproved(@Param("contractId") Long contractId);

    // 按顾客ID和状态统计合同数量
    Long countByCustomerIdAndStatus(Long customerId, String status);
}